PostgresoL Blog
When you’ve got a slow Postgres query, EXPLAIN and its parameters are incredibly useful for working out why.
However, the information returned can be difficult and time-consuming to interpret, especially for more complex queries. Over the years, people have built quite a few tools for visualizing Postgres query plans. As one of those people, I’m a little incredibly biased, but as a fan of many of the others, I hope to do them justice.
Popular EXPLAIN visualization tools
Text format
I’ve come to appreciate the default TEXT format more and more over the years.
A screenshot of a query plan in TEXT format
While it can be tricky to learn to read, it is well-designed, always available, and impressively compact. Since Postgres 9.0 we can also request query plans in machine-readable formats like JSON, YAML, and XML, but these are far less compact.
In text format, you’ll see little indentation arrows ( -> ) denoting the tree structure, and then statistics on a per-operation basis. Many of these statistics are inclusive of their child operations, and several are per-loop averages, meaning you need to know a fair bit before being able to calculate per-operation timings (or rows, or buffers). I also personally find large numbers tricky to grok, since they don’t contain thousand separators. For learning how to read these query plans, I recommend this post from the team at Thoughbot, this talk by Josh Berkus, and our EXPLAIN glossary. Learning what to actually do about issues is a whole extra ballgame.
Even with its limitations, I know of a few experts who mostly read EXPLAIN plans as they come, without tools, even for long query plans. If you are willing to put in the time and effort, it’s still an incredibly useful format.
I believe it is also the best format for people working on the PostgreSQL source code, when slightly different aspects, like the cost estimates, are of primary importance.
In summary:
Pros: compact, always available, familiar to experts
Cons: difficult to learn to read, no advice on what to do next
Explain Depesz
Hubert “depesz” Lubaczewski is a wonderful member of the Postgres community, and can be found in many online spaces helping folks left right and centre. He also creates useful applications, for himself and others, including explain.depesz.com (which is open source, and can be self-hosted).
A screenshot of a query plan in Explain Depesz (click it for the interactive version)
It was created in 2007 and has been popular, maintained, and improved ever since.
Two big strengths of it are the per-operation timing calculations on the left, and the mostly-preserved-text-format explain on the right (there are some subtle improvements, like thousand separators for large numbers).
A nice feature that many don’t know about is that it also accepts plans in JSON, YAML, and XML formats, while still displaying them in its TEXT format style. Another is that whole subplans can be collapsed by clicking on the parent node.
Other smart features that are more obvious are that it colour codes slow operations, bad row estimates, and calculates throughput when buffers are supplied. Particularly bad things will be highlighted in red, medium things in orange, and mild in yellow.
For a couple of years now, it also gives hints in a limited set of cases, providing advice on what could be done to speed up certain queries.
In summary:
Pros: easy to use, preserves the TEXT format (expert-friendly), calculates per-operation timings, highlights problem areas, free and open source, well maintained
Cons: limited advice on what to do next
Tatiyants PEV
Back in 2016, Alex Tatiyants created a tool called PEV (Postgres Explain Visualizer). A web version is hosted at tatiyants.com/pev, but it is also free and open source.
A screenshot of a query plan in PEV
PEV quickly became popular, I suspect thanks to its beautifully simple design.
It (only) accepts JSON format query plans, displaying them as a tree. It does several neat things while keeping the interface simple, including adding descriptions, and highlighting the most expensive operations by time, by rows, and by estimated cost.
Even the hosted version processes and stores the query plans locally, which is fantastic for privacy, but of course does mean that the URLs can’t be shared.
With no updates for more than 8 years, it has mostly been replaced by the next tool in this list, but I do occasionally come across people who prefer it still.
In summary:
Pros: easy to use, simple and visual (beginner-friendly), calculates per-operation timings, highlights problem areas, free and open source
Cons: no longer maintained, no advice on what to do next, doesn’t support TEXT format plans
Explain Dalibo (PEV2)
In 2019, after work on PEV had stopped, Pierre Giraud from Dalibo started work on a fork/rewrite of it, graciously calling it PEV2. There’s a hosted version at explain.dalibo.com, and it is free and open source.
A screenshot of a query plan in Explain Dalibo (click it for the interactive version)
One big improvement over PEV is that it supports both TEXT and JSON format plans.
Another great addition is the side-panel which can show visualizations per statistic — for example time, rows, costs, or buffers.
On the hosted version, plans are stored server-side by default, which enables sharing.
It also borrows from Explain Depesz with very similar colour-coding of issues. It will point out several more categories of issues with different symbols, albeit with less advice on what to do next.
In summary:
Pros: easy to use, visual (beginner-friendly), calculates per-operation timings, highlights quite a few types of problems, free and open source, well maintained
Cons: limited advice on what to do next
pgMustard
In 2018, David Conlin and I started work on pgMustard, another tool that visualizes query plans, but also gives performance advice.
A screenshot of a query plan in pgMustard (click it for the interactive version)
Unlike the others discussed so far, pgMustard is commercial and closed source, but it does have a free trial (and reasonable pricing for both individuals and teams).
It supports TEXT and JSON format query plans, but requires at least EXPLAIN ANALYZE (with timing data).
It visualizes the query plan as a tree (while mimicking the TEXT format structure), and adds a chart (of sorts) above the tree to show the per-operation timing breakdown.
Tips are added for a whole host of performance issues, which are scored based on an estimate of their time-saving potential. These tips describe issues in developer-friendly language, aiming to help people solve their issue without requiring further help.
Some relatively subtle features are some of my favourites — it collapses fast sections of query plans by default (which is especially helpful for large plans), and tries to make it clear when the query is already pretty fast or efficient (low scoring tips, or none at all).
One important aspect that we have spent more effort on than I care to admit, is calculating per-operation timings. It’s a deeper topic than I thought it would be, but making an extra effort on these has made a big difference in some cases.
We also support the latest EXPLAIN features, including SERIALIZE and MEMORY (added in PostgreSQL 17).
Finally, in the past couple of years, we’ve added a public API, allowing people to automatically score and save plans, for things like benchmarking or prioritization.
Pros: easy to use, visual (beginner-friendly), calculates per-operation timings, highlights quite a few types of problem, advice on what to do next, well supported
Cons: costs money, requires timing data
Others
There are lots more explain visualization tools out there, including several IDEs that include them, many of which look similar to this one from pgAdmin:
A screenshot of a query plan in pgAdmin 4
These tend to focus on displaying the information rather than giving guidance, but if you use an IDE already, it’s worth checking out what it has built in.
There is also a relatively popular Russian tool for visualizing query plans that I won’t promote at the moment.
Naturally, people are also now trying out LLM chatbots for getting help interpreting query plans. They tend to suffer from the usual LLM issues, often suggesting some things that would help, some that wouldn’t, and some that would be harmful. It can be difficult to know the difference, especially for less experienced folk. Overall, I don’t yet think they save more time than they waste (for this purpose).
There are also at least a couple of monitoring tools that have added their own explain visualizations, including pganalyze and Google Cloud SQL. They approach the problem a little differently, but are well worth checking out.
Finally, if any of you are unlucky enough to also use MySQL (sorry), I highly recommend trying Tobias Petry’s MySQL Explain tool.
Conclusion
There are lots of options out there for visualizing your query plans, and ultimately for helping you speed up your queries. They each have their own strengths, and hopefully you can find at least one that works well for you.
As a fun side note, the screenshots above are from the same query plan!
Industries
Get In Touch
Call: +86 134 3972 1478
email: bd@taryartar.com
Wechat: laohouzi999
WhatsApp@: 13439721478
Company address: Room 1509, 12th Floor, 3rd to 17th Floor, Building 4, Courtyard 1, Hangfeng Road, Fengtai District, Beijing
